package l2ft.gameserver.model.entity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;

import l2ft.commons.dao.JdbcEntityState;
import l2ft.commons.text.PrintfFormat;
import l2ft.gameserver.Config;
import l2ft.gameserver.dao.ItemsDAO;
import l2ft.gameserver.database.DatabaseFactory;
import l2ft.gameserver.database.MySql;
import l2ft.gameserver.listener.actor.player.OnAnswerListener;
import l2ft.gameserver.model.GameObjectsStorage;
import l2ft.gameserver.model.Player;
import l2ft.gameserver.model.World;
import l2ft.gameserver.model.Zone;
import l2ft.gameserver.model.base.Experience;
import l2ft.gameserver.model.entity.events.GlobalEvent;
import l2ft.gameserver.model.entity.olympiad.Olympiad;
import l2ft.gameserver.model.items.ItemInstance;
import l2ft.gameserver.model.items.ItemInstance.ItemLocation;
import l2ft.gameserver.model.pledge.Clan;
import l2ft.gameserver.network.authcomm.AuthServerCommunication;
import l2ft.gameserver.network.authcomm.gspackets.ChangePassword;
import l2ft.gameserver.network.l2.components.CustomMessage;
import l2ft.gameserver.network.l2.components.SystemMsg;
import l2ft.gameserver.network.l2.s2c.CharInfo;
import l2ft.gameserver.network.l2.s2c.ConfirmDlg;
import l2ft.gameserver.network.l2.s2c.UserInfo;
import l2ft.gameserver.taskmanager.tasks.TaskVariable.TaskType;
import l2ft.gameserver.utils.Log;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class CharacterControlPanel
{
	private static final Logger _log = LoggerFactory.getLogger(CharacterControlPanel.class);
	private static CharacterControlPanel _instance;
	
	private static final int INVENTORY_EXPAND = 40;
	private static final int WAREHOUSE_EXPAND = 50;
	//private static final int CLAN_WAREHOUSE_EXPAND = 80;
	public static final PrintfFormat cfg_row = new PrintfFormat("<table><tr><td width=5></td><td width=120>%s:</td><td width=100>%s</td></tr></table>");
	public static final PrintfFormat cfg_button = new PrintfFormat("<button width=%d back=\"L2UI_CT1.Button_DF_Down\" fore=\"L2UI_CT1.Button_DF\" action=\"bypass changeByPass%s\" value=\"%s\">");
	
	private CharacterControlPanel()
	{
		loadPrivateMessages();
	}
	
	public String useCommand(Player activeChar, String text, String bypass)
	{
		String[] param = text.split(" ");
		if(param.length == 2)
		{
			if(param[0].equalsIgnoreCase("lang"))
				if(param[1].equalsIgnoreCase("en"))
					activeChar.setVar("lang@", "en", -1);
				else if(param[1].equalsIgnoreCase("ru"))
					activeChar.setVar("lang@", "ru", -1);

			if(param[0].equalsIgnoreCase("noe"))
				if(param[1].equalsIgnoreCase("on"))
					activeChar.setVar("NoExp", "1", -1);
				else if(param[1].equalsIgnoreCase("of"))
					activeChar.unsetVar("NoExp");

			if(param[0].equalsIgnoreCase(Player.NO_TRADERS_VAR))
				if(param[1].equalsIgnoreCase("on"))
				{
					activeChar.setNotShowTraders(true);
					activeChar.setVar(Player.NO_TRADERS_VAR, "1", -1);
				}
				else if(param[1].equalsIgnoreCase("of"))
				{
					activeChar.setNotShowTraders(false);
					activeChar.unsetVar(Player.NO_TRADERS_VAR);
				}

			if(param[0].equalsIgnoreCase(Player.NO_ANIMATION_OF_CAST_VAR))
				if(param[1].equalsIgnoreCase("on"))
				{
					activeChar.setNotShowBuffAnim(true);
					activeChar.setVar(Player.NO_ANIMATION_OF_CAST_VAR, "1", -1);
				}
				else if(param[1].equalsIgnoreCase("of"))
				{
					activeChar.setNotShowBuffAnim(false);
					activeChar.unsetVar(Player.NO_ANIMATION_OF_CAST_VAR);
				}

			if(param[0].equalsIgnoreCase("noShift"))
				if(param[1].equalsIgnoreCase("on"))
					activeChar.setVar("noShift", "1", -1);
				else if(param[1].equalsIgnoreCase("of"))
					activeChar.unsetVar("noShift");

			if(param[0].equalsIgnoreCase("translit"))
				if(param[1].equalsIgnoreCase("on"))
					activeChar.setVar("translit", "tl", -1);
				else if(param[1].equalsIgnoreCase("la"))
					activeChar.setVar("translit", "tc", -1);
				else if(param[1].equalsIgnoreCase("of"))
					activeChar.unsetVar("translit");

			if(param[0].equalsIgnoreCase("autoloot"))
				activeChar.setAutoLoot(Boolean.parseBoolean(param[1]));

			if(param[0].equalsIgnoreCase("autoloota"))
				activeChar.setAutoLootOnlyAdena(Boolean.parseBoolean(param[1]));

			if(param[0].equalsIgnoreCase("autolooth"))
				activeChar.setAutoLootHerbs(Boolean.parseBoolean(param[1]));

			if(param[0].equalsIgnoreCase("repairCharacter"))
				repairCharacter(activeChar, param[1]);

			if(param[0].equalsIgnoreCase("changeName"))
			{
				boolean finished = setNewName(activeChar, param[1]);
				if(finished)
				{
					activeChar.sendMessage("Your nickname has been changed!");
					return null;
				}
				else
					return "cfgName.htm";
			}
			
			if(param[0].equalsIgnoreCase("showPMs"))
				return "cfgMessages.htm";
		}
		else
		{
			if(param[0].equalsIgnoreCase("delevel"))
				askQuestion(activeChar, "Do you want to decrease level by one?", Service.delevel);
			
			if(param[0].equalsIgnoreCase("changeSex"))
				askQuestion(activeChar, "Do you want to change your sex?", Service.charSex);
			
			if(param[0].equalsIgnoreCase("showPenalties"))
				return "cfgPenalties.htm";
			
			if(param[0].equalsIgnoreCase("removePk"))
				askQuestion(activeChar, "Do you want to decrease PK point by one?", Service.removePk);
			
			if(param[0].equalsIgnoreCase("showPassword"))
				return "cfgPassword.htm";
			
			if(param[0].equals("changePassword"))
			{
				StringTokenizer st = new StringTokenizer(text, " | ");
				String[] passes = new String[st.countTokens()];
				for(int i = 0;i<passes.length;i++)
				{
					passes[i] = st.nextToken();
				}
				String newDialog = checkNewPassword(activeChar, passes);
				if(newDialog == null)
					return null;
				else
					return newDialog;
			}
			
			if(param[0].equalsIgnoreCase("showName"))
				return "cfgName.htm";
			
			if(param[0].equalsIgnoreCase("offlineStore"))
			{
				checkOfflineStore(activeChar);
				return null;
			}

			if(param[0].equalsIgnoreCase("showRepair"))
				return "cfgRepair.htm";
			
			if(param[0].equalsIgnoreCase("expInventory"))
				askQuestion(activeChar, "Do you want to expand inventory by "+INVENTORY_EXPAND+" slots?", Service.expInventory);
			
			if(param[0].equalsIgnoreCase("expWarehouse"))
				askQuestion(activeChar, "Do you want to expand warehouse by "+WAREHOUSE_EXPAND+" slots?", Service.expWarehouse);
			
			/*if(param[0].equalsIgnoreCase("expClanWarehouse"))
			{
				if(activeChar.getClan() != null && activeChar.getClan().getLeaderId() == activeChar.getObjectId())
					if(activeChar.getClan().getWhBonus() <= 0)
						askQuestion(activeChar, "Do you want to expand clan warehouse by "+CLAN_WAREHOUSE_EXPAND+" slots?", Service.expClanWarehouse);
					else
						activeChar.sendMessage("Clan warehouse has already been expanded!");
				else
					activeChar.sendMessage("Only clan leaders may expand clan warehouses!");
			}*/

			if(param[0].equalsIgnoreCase("penaltyDissolveAlly"))
				askQuestion(activeChar, "Do you want to remove dissolve ally penalty?", Service.penaltyDissolveAlly);

			if(param[0].equalsIgnoreCase("penaltyLeaveAlly"))
				askQuestion(activeChar, "Do you want to remove leave ally penalty?", Service.penaltyLeaveAlly);

			if(param[0].equalsIgnoreCase("penaltyExpelledMember"))
				askQuestion(activeChar, "Do you want to remove expelled member penalty?", Service.penaltyExpelledMember);

			if(param[0].equalsIgnoreCase("penaltyLeaveClan"))
				askQuestion(activeChar, "Do you want to remove leave clan penalty?", Service.penaltyLeaveClan);

			if(param[0].equalsIgnoreCase("penaltyDestroyClan"))
				askQuestion(activeChar, "Do you want to remove destroy clan penalty?", Service.penaltyDestroyClan);
		}
		
		return "char.htm";
	}
	
	public String replacePage(String currentPage, Player activeChar, String additionalText, String bypass)
	{
		currentPage = currentPage.replaceFirst("%online%", String.valueOf(GameObjectsStorage.getAllPlayersCount()));//TODO config for *x
		currentPage = currentPage.replaceFirst("%lang%", activeChar.getVar("lang@").toUpperCase());
		currentPage = currentPage.replaceFirst("%noe%", activeChar.getVarB("NoExp") ? "On" : "Off");
		currentPage = currentPage.replaceFirst("%notraders%", activeChar.getVarB("notraders") ? "On" : "Off");
		currentPage = currentPage.replaceFirst("%notShowBuffAnim%", activeChar.getVarB("notShowBuffAnim") ? "On" : "Off");
		currentPage = currentPage.replaceFirst("%noShift%", activeChar.getVarB("noShift") ? "On" : "Off");
		if(currentPage.contains("%charsOnAccount%"))
			currentPage = currentPage.replaceFirst("%charsOnAccount%", getCharsOnAccount(activeChar.getName(), activeChar.getAccountName()));
		if(currentPage.contains("%privateMessages%"))
			currentPage = currentPage.replace("%privateMessages%", getCharacterPrivateMessages(activeChar, Integer.parseInt(additionalText), bypass));
		if(currentPage.contains("%penalties%"))
			currentPage = currentPage.replace("%penalties%", getPenaltyButtons(activeChar, bypass));
		String additional = "";

		if(Config.AUTO_LOOT_INDIVIDUAL)
		{
			String bt;
			if(activeChar.isAutoLootEnabled())
				bt = cfg_button.sprintf(new Object[] { 100, "autoloot false", new CustomMessage("common.Disable", activeChar).toString() }).replace("changeByPass", bypass);
			else
				bt = cfg_button.sprintf(new Object[] { 100, "autoloot true", new CustomMessage("common.Enable", activeChar).toString() }).replace("changeByPass", bypass);
			additional += cfg_row.sprintf(new Object[] { "Auto-loot", bt });

			if(activeChar.isAutoLootOnlyAdenaEnabled())
				bt = cfg_button.sprintf(new Object[] { 100, "autoloota false", new CustomMessage("common.Disable", activeChar).toString() }).replace("changeByPass", bypass);
			else
				bt = cfg_button.sprintf(new Object[] { 100, "autoloota true", new CustomMessage("common.Enable", activeChar).toString() }).replace("changeByPass", bypass);
			additional += cfg_row.sprintf(new Object[] { "Auto-loot only Adena", bt });

			if(activeChar.isAutoLootHerbsEnabled())
				bt = cfg_button.sprintf(new Object[] { 100, "autolooth false", new CustomMessage("common.Disable", activeChar).toString() }).replace("changeByPass", bypass);
			else
				bt = cfg_button.sprintf(new Object[] { 100, "autolooth true", new CustomMessage("common.Enable", activeChar).toString() }).replace("changeByPass", bypass);
			additional += cfg_row.sprintf(new Object[] { "Auto-loot herbs", bt });
		}

		currentPage = currentPage.replaceFirst("%additional%", additional);

		StringBuilder events = new StringBuilder();
		for(GlobalEvent e : activeChar.getEvents())
			events.append(e.toString()).append("<br>");
		currentPage = currentPage.replace("%events%", events.toString());
		
		return currentPage;
	}
	
	private void askQuestion(Player player, String question, Service service)
	{
		ConfirmDlg packet = new ConfirmDlg(SystemMsg.S1, 120000).addString(question);
		player.ask(packet, new ServiceAnswer(player, service));
	}
	
	private enum Service {
		delevel,
		removePk,
		expInventory,
		expWarehouse,
		//expClanWarehouse,
		charSex,
		penaltyDissolveAlly,
		penaltyLeaveAlly,
		penaltyExpelledMember,
		penaltyLeaveClan,
		penaltyDestroyClan
	}
	
	private class ServiceAnswer implements OnAnswerListener
	{
		private Player _player;
		private Service _service;
		private ServiceAnswer(Player player, Service service)
		{
			_player = player;
			_service = service;
		}
		@Override
		public void sayYes()
		{
			switch(_service)
			{
			case delevel:
				if(_player.getLevel() > 1)
				{
					long lostExp = _player.getExp() - (Experience.getExpForLevel(_player.getLevel())-100);
					_player.addExpAndSp(-lostExp, 0);
					_player.sendMessage("Your level has been decreased by one!");
					
					Log.LogAction(_player, "CCP", "Decreased Level by 1 using CCP");
				}
				else
					_player.sendMessage("Can't do that sir");
				break;
			case removePk:
				if(_player.getPkKills() > 0)
				{
					_player.setPkKills(_player.getPkKills()-1);
					_player.sendUserInfo();
					_player.sendMessage("PK points has been decreased by one!");
					
					Log.LogAction(_player, "CCP", "Decreased Pk Kills by 1 using CCP");
				}
				else
					_player.sendMessage("You need to have at least 1 PK point, to use this function");
				break;
			case expInventory:
				if(_player.getVar("ExpandInventory") == null)
				{
					_player.setExpandInventory(INVENTORY_EXPAND);
					_player.sendMessage("Your inventory has been expanded!");
					_player.setVar("ExpandInventory", INVENTORY_EXPAND, -1);
					_player.sendUserInfo();
					
					Log.LogAction(_player, "CCP", "Expanded Inventory using CCP");
				}
				else
					_player.sendMessage("You have already reached the maximum inventory slots");
				break;
			case expWarehouse:
				if(_player.getVar("ExpandWarehouse") == null)
				{
					_player.setExpandWarehouse(WAREHOUSE_EXPAND);
					_player.sendMessage("Your warehouse has been expanded!");
					_player.setVar("ExpandWarehouse", WAREHOUSE_EXPAND, -1);
					
					Log.LogAction(_player, "CCP", "Expanded Warehouse using CCP");
				}
				else
					_player.sendMessage("You have already reached the maximum warehouse slots");
				break;
			/*case expClanWarehouse:
				if(_player.getClan() != null && _player.getClan().getLeaderId() == _player.getObjectId())
				{
					_player.getClan().setWhBonus(CLAN_WAREHOUSE_EXPAND);
					_player.sendMessage("Clan warehouse has been expanded!");
					
					Log.LogAction(_player, "CCP", "Expanded Clan Warehouse using CCP");
				}
				break;*/
			case charSex:
				_player.changeSex();
				_player.sendPacket(new UserInfo(_player));
				_player.broadcastPacket(new CharInfo(_player));
				_player.broadcastCharInfo();
				_player.sendMessage("Your sex has been changed!");
				
				Log.LogAction(_player, "CCP", "Changed Sex Using CCP");
				break;
			case penaltyDissolveAlly:
				if(_player.getClan() != null)
				{
					_player.getClan().setDissolvedAllyTime(0);
					_player.sendMessage("Dissolve ally penalty has been removed!");
					
					Log.LogAction(_player, "CCP", "Removed Dissolve Ally Penalty using CCP");
				}
				break;
			case penaltyLeaveAlly:
				if(_player.getClan() != null)
				{
					_player.getClan().setLeavedAllyTime(0);
					_player.sendMessage("Leave ally penalty has been removed!");
					
					Log.LogAction(_player, "CCP", "Removed Leave Ally Penalty using CCP");
				}
				break;
			case penaltyExpelledMember:
				if(_player.getClan() != null)
				{
					_player.getClan().setExpelledMemberTime(0);
					_player.sendMessage("Expelled member penalty has been removed!");
					
					Log.LogAction(_player, "CCP", "Removed Rxpelled Member penalty using CCP");
				}
				break;
			case penaltyDestroyClan:
				_player.setDeleteClanTime(0);
				_player.sendMessage("Destroy clan penalty has been removed!");
				
				Log.LogAction(_player, "CCP", "Removed Destroy Clan Penalty using CCP");
				break;
			case penaltyLeaveClan:
				_player.setLeaveClanTime(0);
				_player.sendMessage("Leave clan penalty has been removed!");
				
				Log.LogAction(_player, "CCP", "Removed Clan Leave penalty using CCP");
				break;
			}
		}

		@Override
		public void sayNo()
		{}
	}

	private CharSequence getPenaltyButtons(Player activeChar, String bypass)
	{
		long currentTime = System.currentTimeMillis();
		List<String> buttons = new ArrayList<String>();
		if(activeChar.isClanLeader())
		{
			Clan clan = activeChar.getClan();
			if(clan.getDissolvedAllyTime() > currentTime)
			{
				buttons.add("<button width=100 height=20 back=\"L2UI_CT1.Button_DF_Down\" fore=\"L2UI_CT1.Button_DF\" action=\"bypass "+bypass+"penaltyDissolveAlly\" value=\"Dissolved ally\">");
			}
			if(clan.getLeavedAllyTime() > currentTime)
			{
				buttons.add("<button width=100 height=20 back=\"L2UI_CT1.Button_DF_Down\" fore=\"L2UI_CT1.Button_DF\" action=\"bypass "+bypass+"penaltyLeaveAlly\" value=\"Leaved ally\">");
			}
			if(clan.getExpelledMemberTime() > currentTime)
			{
				buttons.add("<button width=100 height=20 back=\"L2UI_CT1.Button_DF_Down\" fore=\"L2UI_CT1.Button_DF\" action=\"bypass "+bypass+"penaltyExpelledMember\" value=\"Expelled member\">");
			}
		}
		else
		{
			if(activeChar.getLeaveClanTime() > currentTime)
			{
				buttons.add("<button width=100 height=20 back=\"L2UI_CT1.Button_DF_Down\" fore=\"L2UI_CT1.Button_DF\" action=\"bypass "+bypass+"penaltyLeaveClan\" value=\"Leaved clan\">");
			}
			if(activeChar.getDeleteClanTime() > currentTime)
			{
				buttons.add("<button width=100 height=20 back=\"L2UI_CT1.Button_DF_Down\" fore=\"L2UI_CT1.Button_DF\" action=\"bypass "+bypass+"penaltyDestroyClan\" value=\"Destroyed clan\">");
			}
		}
		
		String penalties = "You currently don't have any penalties!";
		
		for(int i = 0;i<buttons.size();i++)
		{
			if(i == 0)
				penalties = "<table width=260>";
			if(i % 2 == 0)
				penalties += "<tr>";
			penalties += "<td>"+buttons.get(i)+"</td>";
			if(i % 2 == 1 && i > 0)
				penalties += "</tr>";
		}
		if(buttons.size() % 2 == 1)
			penalties += "<td></td></tr>";
		if(buttons.size() > 0)
			penalties += "</table>";
		return penalties;
	}
	
	private boolean setNewName(Player player, String newName)
	{
		if(MySql.simple_get_int("count(*)", "characters", "`char_name` like '" + newName + "'") > 0)
		{
			player.sendMessage("Name already exist.");
			return false;
		}
		String prevName = player.getName();
		player.reName(newName);
		
		Log.LogAction(player, "CCP", "!!!!!!!!!!!!!!!!!!!!!! CHANGED NAME FROM:"+prevName+" TO:"+newName+" BY USING CCP !!!!!!!!!!!!!!!!!!!!!!!");
		return true;
	}
	
	private void checkOfflineStore(Player activeChar)
	{
		if(!Config.SERVICES_OFFLINE_TRADE_ALLOW)
			return;

		if(activeChar.getOlympiadObserveGame() != null || activeChar.getOlympiadGame() != null  || Olympiad.isRegisteredInComp(activeChar) || activeChar.getKarma() > 0)
		{
			activeChar.sendActionFailed();
			return;
		}
	
		if(activeChar.getLevel() < Config.SERVICES_OFFLINE_TRADE_MIN_LEVEL)
		{
			activeChar.sendMessage("You need to have at least "+Config.SERVICES_OFFLINE_TRADE_MIN_LEVEL+" level to use offline trade!");
			return;
		}
	
		if(!activeChar.isInZone(Zone.ZoneType.offshore) && Config.SERVICES_OFFLINE_TRADE_ALLOW_OFFSHORE)
		{
			activeChar.sendMessage("Offline trades can be used only on Offshore zones!");
			return;
		}
	
		if(!activeChar.isInStoreMode())
		{
			activeChar.sendMessage("You need to set a store first!");
			return;
		}

		if(activeChar.taskExists(TaskType.Chat_ban))
		{
			activeChar.sendMessage("You cannot do that while having chat ban!");
			return;
		}
	
		if(activeChar.isActionBlocked(Zone.BLOCKED_ACTION_PRIVATE_STORE))
		{
			activeChar.sendMessage("Its not offline trade zone!");
			return;
		}
	
		if(Config.SERVICES_OFFLINE_TRADE_PRICE > 0 && Config.SERVICES_OFFLINE_TRADE_PRICE_ITEM > 0)
		{
			if(activeChar.getInventory().getItemByItemId(Config.SERVICES_OFFLINE_TRADE_PRICE_ITEM).getCount() < Config.SERVICES_OFFLINE_TRADE_PRICE)
			{
				activeChar.sendMessage("You don't have necessary items!");
				return;
			}
			activeChar.getInventory().removeItemByItemId(Config.SERVICES_OFFLINE_TRADE_PRICE_ITEM, Config.SERVICES_OFFLINE_TRADE_PRICE);
		}
		
		Log.LogAction(activeChar, "Log_Out", "Started Offline shopping!");
	
		activeChar.offline();
		
	}

	private String checkNewPassword(Player activeChar, String[] passes)
	{
		if(passes.length != 4)
		{
			activeChar.sendMessage("Fill all the fields!");
			return "cfgPassword.htm";
		}
		
		String current = passes[1];
		String new1 = passes[2];
		String new2 = passes[3];

		if(!new1.equals(new2))
		{
			activeChar.sendMessage("New passwords doesn't match!");
			return "cfgPassword.htm";
		}

		if(new1.equals(current))
		{
			activeChar.sendMessage("New password cannot be the same as old password!");
			return "cfgPassword.htm";
		}

		if(new1.length() < 5 || new1.length() > 20)
		{
			activeChar.sendMessage("Incorrect sze!");
			return "cfgPassword.htm";
		}
		
		AuthServerCommunication.getInstance().sendPacket(new ChangePassword(activeChar.getAccountName(), current, new1, "null"));
		return null;
	}
	
	private boolean repairCharacter(Player activeChar, String target)
	{
		if(activeChar.getName().equalsIgnoreCase(target))
		{
			activeChar.sendMessage("You cannot repair yourself!");
			return false;
		}

		int objId = 0;

		for(Map.Entry<Integer, String> e : activeChar.getAccountChars().entrySet())
		{
			if(e.getValue().equalsIgnoreCase(target))
			{
				objId = e.getKey();
				break;
			}
		}

		if(objId == 0)
		{
			activeChar.sendMessage("Incorrect character!");
			return false;
		}
		else if(World.getPlayer(objId) != null)
		{
			activeChar.sendMessage("Selected character is online!");
			return false;
		}

		try (Connection con = DatabaseFactory.getConnection())
		{
			int karma = 0;
			try(PreparedStatement statement = con.prepareStatement("SELECT karma FROM characters WHERE obj_Id=?"))
			{
				statement.setInt(1, objId);
				
				try(ResultSet rs = statement.executeQuery())
				{
					rs.next();
		
					karma = rs.getInt("karma");
				}
			}

			if(karma > 0)
			{
				try(PreparedStatement statement = con.prepareStatement("UPDATE characters SET x=17144, y=170156, z=-3502 WHERE obj_Id=?"))
				{
					statement.setInt(1, objId);
					statement.execute();
				}
			}
			else
			{
				try(PreparedStatement statement = con.prepareStatement("UPDATE characters SET x=77816, y=148632, z=-3622 WHERE obj_Id=?"))
				{
					statement.setInt(1, objId);
					statement.execute();
				}

				Collection<ItemInstance> items = ItemsDAO.getInstance().getItemsByOwnerIdAndLoc(objId, ItemLocation.PAPERDOLL);
				for(ItemInstance item : items)
				{
					if(item.isEquipped())
					{
						item.setEquipped(false);
						item.setJdbcState(JdbcEntityState.UPDATED);
						item.update();
					}
				}
			}

			try(PreparedStatement statement = con.prepareStatement("DELETE FROM character_variables WHERE obj_id=? AND type='user-var' AND name='reflection'"))
			{
				statement.setInt(1, objId);
				statement.execute();
			}
			
			Log.LogAction(activeChar, "CCP", "Repaired Character:"+target);

			activeChar.sendMessage("Character has been repaired!");
			return true;
		}
		catch(Exception e)
		{
			_log.error("", e);
			return false;
		}
	}

	private String getCharsOnAccount(String myCharName, String accountName) 
	{
		List<String> chars = new ArrayList<String>();
		
		try (Connection con = DatabaseFactory.getConnection();
				PreparedStatement statement = con.prepareStatement("SELECT char_name FROM characters WHERE account_name=?"))
		{
			statement.setString(1, accountName);
			
			try(ResultSet rset = statement.executeQuery())
			{
				while(rset.next())
				{
					chars.add(rset.getString("char_name"));
				}
			}
		}
		catch(Exception e)
		{
			_log.error("", e);
		}
		
		String result = "";
		for(String charName : chars)
			if(!charName.equalsIgnoreCase(myCharName))
				result += charName + ";";
		
		if(result.isEmpty())
			return "";
		
		return result.substring(0, result.length()-1);
	}
	
	private static final int PMS_PER_PAGE = 10;

	
	private synchronized String getCharacterPrivateMessages(Player player, int page, String bypass)
	{
		List<PrivateMessage> myMessages = new ArrayList<PrivateMessage>();
		String myName = player.getName();
		boolean community = bypass.startsWith("_");
		for(PrivateMessage msg : privateMessages)
		{
			if(msg._receiverName.equals(myName) || msg._senderName.equals(myName))
				myMessages.add(msg);
		}
		
		Collections.sort(myMessages, new PrivateMessageComparator());
		String result = "";
		
		for(int i = (page-1)*PMS_PER_PAGE;i<Math.min(myMessages.size(), (page-1)*PMS_PER_PAGE + PMS_PER_PAGE);i++)
		{
			PrivateMessage msg = myMessages.get(i);
			boolean iAmSender = msg._senderName.equals(player.getName());
			Calendar c = Calendar.getInstance();
			c.setTimeInMillis(msg._time);
			result += "<img src=\"L2UI.SquareGray\" width="+(community ? "750" : "275")+" height=2>";
			result += "<table bgcolor=333333 width="+(community ? "750" : "275")+">";
			result += "<tr>";
			result += "<td><center><font color=\"00FF00\">"+(iAmSender ? "To" : "From")+": </font>";
			result += (iAmSender ? msg._receiverName : msg._senderName);
			result += "</center></td>";
			result += "<td><center><font color=\"00FF00\">At: </font>";
			result += c.get(Calendar.DAY_OF_MONTH)+"/"+c.get(Calendar.MONTH)+"/"+c.get(Calendar.YEAR);
			result += " / ";
			result += c.get(Calendar.HOUR_OF_DAY)+":"+c.get(Calendar.MINUTE)+":"+c.get(Calendar.SECOND);
			result += "</center></td>";
			result += "</tr>";
			result += "</table>";
			result += "<img src=\"L2UI.SquareGray\" width="+(community ? "750" : "275")+" height=2>";
			result += "<table bgcolor=333333 width="+(community ? "750" : "275")+">";
			result += "<tr>";
			result += "<td width="+(community ? "750" : "275")+"><font color=\"FF00FF\"><center>";
			result += msg._text;
			result += "</center></font></td>";
			result += "</tr>";
			result += "</table>";
		}
		
		int pagesCount = (int) Math.ceil(((double)myMessages.size())/PMS_PER_PAGE);
		
		result += "<br><br><table><tr>";
		
		int all = Math.min(PMS_PER_PAGE, pagesCount);
		
		for(int i = Math.max(1, Math.min(pagesCount-4, page - 2)) ; all > 0 ; i++)
		{
			if(i == page)
				result += "<td><center>"+i+"</center></td>";
			else
				result += "<td><center><button width=20 height=20 back=\"L2UI_CT1.Button_DF_Down\" fore=\"L2UI_CT1.Button_DF\" action=\"bypass "+bypass+"showPMs "+i+"\" value=\""+i+"\"></center></td>";
			all--;
		}
		
		result += "</tr></table><br>";
		
		return result;
	}
	
    private class PrivateMessageComparator implements Comparator<PrivateMessage>
    {
		@Override
		public int compare(PrivateMessage o1, PrivateMessage o2)
		{
			return (int) (o2._time - o1._time);
	    }
    }
    
    private List<PrivateMessage> privateMessages = new ArrayList<PrivateMessage>();
	
    public synchronized void addNewPrivateMessage(String sender, String receiver, String message)
    {
    	PrivateMessage msg = new PrivateMessage();
    	msg._senderName = sender;
    	msg._receiverName = receiver;
    	msg._time = System.currentTimeMillis();
    	msg._text = message;
    	
    	privateMessages.add(msg);
    }
    
    private void loadPrivateMessages()
    {
    	try (Connection con = DatabaseFactory.getConnection();
    			PreparedStatement statement = con.prepareStatement("SELECT * FROM character_private_messages");
    			ResultSet rset = statement.executeQuery())
    	{
    		while(rset.next())
    		{
    			PrivateMessage msg = new PrivateMessage();
    			msg._senderName = rset.getString("sender_name");
    			msg._receiverName = rset.getString("receiver_name");
    			msg._time = rset.getLong("time_of_sending");
    			msg._text = rset.getString("message");
    			privateMessages.add(msg);
    		}
    	}
    	catch(Exception e)
    	{
    		_log.error("error in loadPrivateMessages():", e);
   		}
    }
    
    public void savePrivateMessages()
    {
    	String insert = "INSERT INTO character_private_messages (sender_name, receiver_name, time_of_sending, message) values ";
    	boolean b = false;
    	for(PrivateMessage msg : privateMessages)
    	{
    		insert += "('"+msg._senderName+"','"+msg._receiverName+"',"+msg._time+",'"+msg._text+"'), ";
    		b = true;
    	}
    	if(b)
    		insert = insert.substring(0, insert.length()-2);
    	
		try (Connection con = DatabaseFactory.getConnection())
		{
			
			try(PreparedStatement statement = con.prepareStatement("DELETE FROM character_private_messages"))
			{
				statement.execute();
			}
            
			if(b)
			{
				try(PreparedStatement statement = con.prepareStatement(insert))
				{
					statement.execute();
				}
			}
		}
		catch(SQLException e)
		{
			_log.error("SQL exception while savePrivateMessages.", e);
		}
    }

	private class PrivateMessage
	{
		String _senderName;
		String _receiverName;
		long _time;
		String _text;
	}
	
	public static CharacterControlPanel getInstance()
	{
		if(_instance == null)
			_instance = new CharacterControlPanel();
		return _instance;
	}
}
